package top.shiyiri.preparedstatement;

import org.junit.Test;
import top.shiyiri.connection.ConnectionTest;
import top.shiyiri.util.JDBCUtils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;

/**
 * @author Aunean
 * @date 2022/1/17 12:57
 */
/*
使用 PreparedStatement 来替换 Statement，实现对数据表的增删改查操作

增删改；查
 */
public class PreparedStatementUpdateTest {

    //向customers表添加一个记录
    @Test
    public void test1() {
        Connection connection = null;
        PreparedStatement ps = null;

        try {
            //1.读取配置文件信息
            InputStream inputStream = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");

            Properties properties = new Properties();
            properties.load(inputStream);

            String user = properties.getProperty("user");
            String password = properties.getProperty("password");
            String url = properties.getProperty("url");
            String driverClass = properties.getProperty("driverClass");

            //2.加载驱动
            Class.forName(driverClass);

            //3.获取连接
            connection = DriverManager.getConnection(url, user, password);
            System.out.println(connection);

            //4.预编译sql语句，返回 PreparedStatement 的实例
            String sql = "insert into customers(name, email, birth) values(?,?,?)";
            ps = connection.prepareStatement(sql);

            //5.填充占位符
            ps.setString(1, "张三");
            ps.setString(2, "123@126.com");
            ps.setString(3, "2001-12-12");

            //6.执行操作
            ps.execute();
        } catch (IOException | SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            //7.关闭资源
            try {
                assert ps != null;
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //修改Customers表的一条记录
    @Test
    public void updateTest() {
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            //1.获取数据库的连接
            connection = JDBCUtils.getConnection();

            //2.预编译 sql 语句，调用 PreparedStatement
            String sql = "update customers set birth = ? where name = ?";
            ps = connection.prepareStatement(sql);

            //3.填充占位符
            ps.setString(1, "1999-11-01");
            ps.setString(2, "汪峰");

            //4.执行
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //5.关闭资源
            JDBCUtils.closeResource(connection, ps);
        }
    }

    //通用的增删改操作
    //sql中占位符的个数与可变形参的长度一致
    public static void update(String sql, Object ... args) {
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            //1.获取连接
            connection = JDBCUtils.getConnection();
            //2.预编译SQL语句，返回PreparedStatement的实例
            ps = connection.prepareStatement(sql);
            //3.填充占位符
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i+1, args[i]);
            }
            //执行操作
            /*
            如果执行的是查询操作，有返回结果，则此方法返回 true
            如果执行的是增、删、改操作，没有返回结果，则此方法返回 false
             */
            //ps.execute();
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //关闭资源
            JDBCUtils.closeResource(connection, ps);
        }
    }

    @Test
    public void testUpdate() {
//        String sql = "delete from customers where id = ?";
//        boolean update = update(sql, 3);

//        String sql = "update `order` set order_id = ? where order_name = ?";
//        update(sql, "3", "GG");

        String sql = "insert into `order` values(?,?,?)";
        update(sql, 4, "CC", "2013-12-23");
    }
}
